﻿Imports System.Data.SqlClient
Public Class Form1
    Public conexion As New SqlConnection("Data Source=.\SQLExpress;Initial Catalog=protozoo;Integrated Security=TRUE")
    Public adaptador As New SqlDataAdapter("Select * from usuarios", conexion)
    Public miDataset As New DataSet

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        adaptador.Fill(miDataset, "usuario")
        'mostrar la tabla En el datagrid
        DataGridView1.DataSource = miDataset
        DataGridView1.DataMember = "usuario"
        'mostrar un campo en un textbox
        TextBox1.DataBindings.Add("text", miDataset, "usuarios.nombre")
    End Sub

    Private Sub registro()

        Label1.Text = ((Me.BindingContext(miDataset, "usuarios").Position + 1).ToString + " de " + BindingContext(miDataset, "usuarios").Count.ToString)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        BindingContext(miDataset, "usuarios").Position -= 1
        registro()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        BindingContext(miDataset, "usuarios").Position = 0
        registro()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        BindingContext(miDataset, "usuarios").Position = 0
        registro()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        BindingContext(miDataset, "usuarios").Position = BindingContext(miDataset, "usuarios").Count
        registro()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Dim comando As SqlCommand
        Dim ordenSql As String = "insert into usuarios (nombre, psw, tipo) values (@nombre, @psw, @tipo)"
        comando = New SqlCommand(ordenSql, conexion)

        'comando.Parameters.AddWithValue("@id_usuario", TextBox1.Text)
        comando.Parameters.AddWithValue("@nombre", TextBox2.Text)
        comando.Parameters.AddWithValue("@psw", TextBox3.Text)
        comando.Parameters.AddWithValue("@tipo", TextBox4.Text)

        'GUARDAR

        conexion.Open()
        comando.ExecuteNonQuery() 'Ejecutar la query
        conexion.Close()

        miDataset.Clear() ' Crear nuevo adaptador
        adaptador.Fill(miDataset, "usuarios")

        DataGridView1.DataSource = miDataset 'mostrar en el datagrid
        DataGridView1.DataMember = "usuarios"

        registro()



    End Sub


    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim comando As SqlCommand
        Dim ordenSql As String = "Update usuarios set nombre=@nombre, psw=@psw, tipo=@tipo where id_usuario=@id_usuario"
        comando = New SqlCommand(ordenSql, conexion)

        comando.Parameters.AddWithValue("@id_usuario", TextBox1.Text)
        comando.Parameters.AddWithValue("@nombre", TextBox2.Text)
        comando.Parameters.AddWithValue("@psw", TextBox3.Text)
        comando.Parameters.AddWithValue("@tipo", TextBox4.Text)

        'GUARDAR

        conexion.Open()
        comando.ExecuteNonQuery() 'Ejecutar la query
        conexion.Close()

        miDataset.Clear() ' Crear nuevo adaptador
        adaptador.Fill(miDataset, "usuarios")

        DataGridView1.DataSource = miDataset 'mostrar en el datagrid
        DataGridView1.DataMember = "usuarios"

        registro()
    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        Dim m As Integer = 0
        Dim pos As Integer = 0

        Dim ordenSql As New SqlCommand("select * from usuarios where nombre = @nombre", conexion)
        ordenSql.Parameters.AddWithValue("@nombre", TextBox2.Text)

        conexion.Open()
        Dim res As SqlDataReader = ordenSql.ExecuteReader
        Try
            Do While res.Read
                If res.Item("nombre") = TextBox2.Text Then
                    TextBox1.Text = res("id_usuario")
                    TextBox2.Text = res("nombre")
                    TextBox3.Text = res("psw")
                    TextBox1.Text = res("tipo")

                    m = 1
                Else
                    pos = pos + 1
                End If


            Loop

            If m = 0 Then
                MsgBox("No hay coincidencias")
            End If

        Catch ex As Exception
            MsgBox("hay un error")
        End Try
        conexion.Close()


    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

        Dim res As Integer
        Dim eliminar As String = "Delete from usuarios where nombre=@nombre"
        Dim comando As New SqlCommand(eliminar, conexion)

        comando.Parameters.AddWithValue("@nombre", TextBox2.Text)
        res = MsgBox("Seguro eliminar?" & TextBox1.Text, 20, "Aviso")
        If res = 6 Then
            conexion.Open()
            comando.ExecuteNonQuery()
            conexion.Close()

            miDataset.Clear()
            adaptador.Fill(miDataset, "usuarios")

            DataGridView1.DataSource = miDataset
            DataGridView1.DataMember = "usuarios"


        End If
    End Sub

    Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click

    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub
End Class